package data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import dataTransferObject.Mascota;


public class JDBCMascotaDAO implements IMascotaDAO {
	IRazaDAO razaDAO;
	
	public JDBCMascotaDAO(){
		razaDAO=new JDBCRazaDAO();
	}
	
	public Mascota selectMascota(int idMascota) {
		Mascota mascota=null;
		
		Connection conn = ConnectionManager.getInstance().CheckOut();
		
		mascota=selectMascota(conn, idMascota);
		
		ConnectionManager.getInstance().CheckIn(conn);
	        
		
	return mascota;
	}
	
	
	public Mascota selectMascota(Connection conn,int idMascota) {
		Mascota mascota=null;
		PreparedStatement stat=null;
		ResultSet resultSet=null;
		String sql="SELECT * FROM mascota WHERE ID_MASCOTA=?";
		try{
		stat = conn.prepareStatement(sql);
		stat.setInt(1, idMascota);
		resultSet= stat.executeQuery();
		
		while(resultSet.next()){
			mascota=new Mascota();
			mascota.setNombre(resultSet.getString("NOMBRE"));
			mascota.setIdMascota(resultSet.getInt("ID_MASCOTA"));
			mascota.setFecha_nacimiento(resultSet.getDate("FECHA_NACIMIENTO")); 
			mascota.setImagen_de_mascota(resultSet.getString("FOTO")); 
			mascota.setSexo(resultSet.getString("SEXO"));
			mascota.setRaza(razaDAO.selectRaza(conn,resultSet.getInt("ID_RAZA")));
			mascota.setPuntuacion(resultSet.getDouble("PUNTUACION"));
		}
		
		
		} catch(SQLException e) { 
			System.out.println("selectMascota Message: " + e.getMessage());
	        System.out.println("selectMascota SQLState: " + e.getSQLState());
	        System.out.println("selectMascota ErrorCode: " + e.getErrorCode());
			
		}finally {
		
			try {
	            if (resultSet != null)
	                resultSet.close();
	            if (stat != null)
	                stat.close();
	        } catch (SQLException e) {
	        } 
		}
		
	return mascota;
	}
	
	
	
	public List<Mascota> selectMascotas(int idUsuario){		
		List<Mascota> lista= new ArrayList<Mascota>();
		Connection conn = ConnectionManager.getInstance().CheckOut();
		
		lista=selectMascotas(conn, idUsuario);
		
		ConnectionManager.getInstance().CheckIn(conn);
	    	
	return lista;	
	}
	
	public List<Mascota> selectMascotas(Connection conn,int idUsuario){		
		List<Mascota> lista= new ArrayList<Mascota>();
		Mascota mascota=null;
		PreparedStatement stat=null;
		ResultSet resultSet=null;
		String sql="SELECT ID_MASCOTA FROM mascotas_us WHERE ID_USUARIO=?";		
		try{		
			stat = conn.prepareStatement(sql);
			stat.setInt(1, idUsuario);
			resultSet= stat.executeQuery();	
			while(resultSet.next()){
				mascota=new Mascota();
				mascota=selectMascota(resultSet.getInt("ID_MASCOTA"));
				lista.add(mascota);			
			}
		
		} catch(SQLException e) { 		
			System.out.println("selectMascotas Message: " + e.getMessage());
	        System.out.println("selectMascotas SQLState: " + e.getSQLState());
	        System.out.println("selectMascotas ErrorCode: " + e.getErrorCode());		
		}finally {		
	        try {       	
	            if (resultSet != null)
	                resultSet.close();
	            if (stat != null)
	                stat.close();
	        } catch (SQLException e) {
	        } 
		}		
	return lista;	
	}
	
	
	
	public List<Mascota> selectRanking() {
		
		Connection conn=ConnectionManager.getInstance().CheckOut();
		PreparedStatement stmt = null;
		List<Mascota> MapResult = new ArrayList<Mascota>();
		
		String sql = "SELECT * FROM mascota, raza WHERE mascota.ID_RAZA=raza.ID_RAZA ORDER BY mascota.PUNTUACION DESC";
		ResultSet result = null;
		
		try {
			
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();
			
			while (result.next()) {
				
				Mascota mascota = new Mascota();
				mascota.setIdMascota(result.getInt("ID_MASCOTA"));
				mascota.setNombre(result.getString("NOMBRE"));
				mascota.setRaza(razaDAO.selectRaza(conn, result.getInt("ID_RAZA")));
				mascota.setFecha_nacimiento(result.getDate("FECHA_NACIMIENTO"));
				mascota.setSexo(result.getString("SEXO"));
				mascota.setPuntuacion(result.getDouble("PUNTUACION"));
				mascota.setImagen_de_mascota(result.getString("FOTO"));

				MapResult.add(mascota);
				
			}
			
		} catch (SQLException e) {	
			
			System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());	
            	
		} finally {							
			
				
				ConnectionManager.getInstance().CheckIn(conn);									
			try {
				
				if (result != null) {
					
					result.close();
				
				}
				
				if (stmt != null) {
					
					stmt.close();
				
				}
				
				
				
				
			
			} catch (Exception e) {
			
			}
		
		}
		
		return MapResult;
	
	}


	public boolean votacionUsuario(int idMascota,int idUsuario){
	
		Connection conn = ConnectionManager.getInstance().CheckOut();
		PreparedStatement stat=null;
		ResultSet resultSet=null;
		Boolean existe=false;
		String sql="SELECT * FROM petsociety.puntuacion WHERE ID_USUARIO=? AND ID_MASCOTA=?";		
		
		try{		
			
			stat = conn.prepareStatement(sql);
			stat.setInt(1, idUsuario);
			stat.setInt(2, idMascota);
			resultSet= stat.executeQuery();	
			if(resultSet.next()){
				
				existe=true;	
			
			}
		
		} catch(SQLException e) { 		
			
			System.out.println("votacionUsuario Message: " + e.getMessage());
	        System.out.println("votacionUsuario SQLState: " + e.getSQLState());
	        System.out.println("votacionUsuario ErrorCode: " + e.getErrorCode());		
		
		}finally {		
			
			ConnectionManager.getInstance().CheckIn(conn);
	        
			try {
				
	            if (resultSet != null)
	                
	            	resultSet.close();
	            if (stat != null)
	                
	            	stat.close();
	       
			} catch (SQLException e) {
	        
			} 
		
		}		
		
	return existe;	

	}
	
	public void insertPuntuacion(int idMascota,int idUsuario,int puntuacion){
		
		Connection conn = ConnectionManager.getInstance().CheckOut();
		Statement stmt=null;
		ResultSet resultSet=null;
		
		try{
		
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet. CONCUR_UPDATABLE);
			resultSet = stmt.executeQuery("SELECT * FROM petsociety.puntuacion");
			resultSet.moveToInsertRow();
			resultSet.updateInt("ID_USUARIO", idUsuario);
			resultSet.updateInt("ID_MASCOTA", idMascota);
			resultSet.updateInt("PUNTUACION", puntuacion);			
			resultSet.insertRow(); 
			resultSet.close(); 
		
		} catch (SQLException e) {
            
			System.out.println("insertOrigen Message: " + e.getMessage());
            System.out.println("insertOrigen SQLState: " + e.getSQLState());
            System.out.println("insertOrigen ErrorCode: " + e.getErrorCode());
        
		} finally {
        	
			ConnectionManager.getInstance().CheckIn(conn);
           
			try {
            	
				if (resultSet != null)
	               
					resultSet.close();
               
				if (stmt != null) 
                   
					stmt.close();
                
            } catch (SQLException e) {
            
            }
        
		}		
				
	}

	public void setPuntuacion(int idMascota,double Puntuacion){
		
		Statement stmt =null;
		Connection conn=ConnectionManager.getInstance().CheckOut();

		try{
			
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet. CONCUR_UPDATABLE);
			stmt.executeUpdate("UPDATE petsociety.mascota SET " +"PUNTUACION=" + Puntuacion+ ""+ " WHERE ID_MASCOTA="+ idMascota);
			
		} catch (SQLException e) {

			System.out.println("setPuntuacion Message: " + e.getMessage());
            System.out.println("setPuntuacion SQLState: " + e.getSQLState());
            System.out.println("setPuntuacion ErrorCode: " + e.getErrorCode());
        
		} finally {
           ConnectionManager.getInstance().CheckIn(conn);
			try {
               
                if (stmt != null) {
                   
                	stmt.close();
                
                }
            
			} catch (SQLException e) {
            
			}
        
		}	
	
	}
	
	public Map<Mascota, Integer> selectAll() {
		
		Connection conn=ConnectionManager.getInstance().CheckOut();
		PreparedStatement stmt = null;
		Map<Mascota, Integer> MapResult = new HashMap<Mascota, Integer>();
		
		String sql = "SELECT * FROM mascota, raza, mascotas_us WHERE mascota.ID_RAZA=raza.ID_RAZA AND mascota.ID_MASCOTA=mascotas_us.ID_MASCOTA ORDER BY mascota.PUNTUACION DESC";
		ResultSet result = null;
		
		try {
			
			
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();
			
		
			
			while (result.next() && MapResult.size()<=6) {
				
				Mascota mascota = new Mascota();
				mascota.setIdMascota(result.getInt("ID_MASCOTA"));
				mascota.setNombre(result.getString("NOMBRE"));
				mascota.setRaza(razaDAO.selectRaza(conn, result.getInt("ID_RAZA")));
				mascota.setFecha_nacimiento(result.getDate("FECHA_NACIMIENTO"));
				mascota.setSexo(result.getString("SEXO"));
				mascota.setPuntuacion(result.getDouble("PUNTUACION"));
				mascota.setImagen_de_mascota(result.getString("FOTO"));
				
				MapResult.put(mascota, result.getInt("ID_USUARIO"));
				
				
				
			}
			
		} catch (SQLException e) {	
			
			System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());	
            	
		} finally {							
			ConnectionManager.getInstance().CheckIn(conn);
														
			try {
				
				if (result != null) {
					
					result.close();
				
				}
				
				if (stmt != null) {
					
					stmt.close();
				
				}
				
				
			
			} catch (Exception e) {
			
			}
		
		}
		
		return MapResult;
	
	}
	
	
	
}
